package org.cncitrus.data.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import orange.database.DBIns;

import org.cncitrus.data.common.Params;
import org.cncitrus.data.manager.ParamsManager;

public class ChinaCBSYConn {
	private String TABLE_NAME = "orange_z_cbsy";
//	private Statement stmt;

	public ChinaCBSYConn() throws SQLException {
//		stmt = OrangeConn.getInstance().getStatement();
	}

	public String load(String[] spec, String[] year, String[] pro)
			throws Exception {
		String sql = "SELECT * FROM " + TABLE_NAME + " WHERE "+Params.ChinaCBSY.year+" IN "
				+ ParamsManager.Array2String(year) + " AND "+Params.ChinaCBSY.pro+" IN "
				+ ParamsManager.Array2String(pro) + " AND "+Params.ChinaCBSY.spec+" IN "
				+ ParamsManager.Array2String(spec);
		Connection conn = DBIns.getInstance().getConnection();
		StringBuffer sb = new StringBuffer();
		try {
			
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
			rs.last();
			sb.append("<resp count=\"" + rs.getRow() + "\"> ");
			rs.beforeFirst();
			while (rs.next()) {
				sb.append("<item year=\"" + rs.getString(Params.ChinaCBSY.year).trim()
						+ "\"pro=\"" + rs.getString(Params.ChinaCBSY.pro).trim()
						+ "\" spec =\"" + rs.getString(Params.ChinaCBSY.spec).trim() + "\" "
						+ "item=\"" + rs.getString(Params.ChinaCBSY.item).trim() + "\" unit=\""
						+ String.valueOf(rs.getString(Params.ChinaCBSY.unit)).trim() + "\" total=\""
						+ String.valueOf(rs.getString(Params.ChinaCBSY.total)).trim() + "\" category=\""
						+ String.valueOf(rs.getString(Params.ChinaCBSY.category)).trim() + "\" />");			
			}
			sb.append("</resp>");
		} catch (Exception e) {
			// TODO: handle exception
		}
		DBIns.getInstance().release(conn);
		
		return sb.toString();
	}
	/**
	 * 
	 * @param year
	 * @param pro
	 * @param spec
	 * @param con 富文本字段 包括很多项
	 * @return
	 * @throws Exception
	 */
	public String insert(String[] year,String[] Class,String[] spec,String[] con) throws Exception{
		
		StringBuffer sb = new StringBuffer();
		Connection conn = DBIns.getInstance().getConnection();
		try {
			sb.append("INSERT INTO "+TABLE_NAME+" ("+Params.ChinaCBSY.year+","+Params.ChinaCBSY.category+","+Params.ChinaCBSY.spec+","+Params.ChinaCBSY.item+") VALUES ");		
			
			String content = ParamsManager.getInsertString(year,Class,spec,con);
			sb.append(content);
			Statement stmt = conn.createStatement();
			stmt.execute(sb.toString());
			sb = new StringBuffer();
			sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
			sb.append("<resp rc=\"0\"/>");		
		} catch (Exception e) {
			// TODO: handle exception
		}
		DBIns.getInstance().release(conn);
		return sb.toString();
	}
}
